﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

-- Add BizTalk Server Groups to Database Roles
--:setvar BtsAdminGroup "CCIDEV\BizTalk Server Administrators"
CREATE USER [$(BtsAdminGroup)] FOR LOGIN [$(BtsAdminGroup)];
GO

EXECUTE sp_addrolemember @rolename = N'ESBPortalAdmin', @membername = [$(BtsAdminGroup)];
GO

GRANT CONNECT TO [$(BtsAdminGroup)] AS [dbo];
GO

--:setvar BtsUserGroup "CCIDEV\BizTalk Application Users"
CREATE USER [$(BtsUserGroup)] FOR LOGIN [$(BtsUserGroup)];
GO

EXECUTE sp_addrolemember @rolename = N'ESBPortal', @membername = [$(BtsUserGroup)];
GO

GRANT CONNECT TO [$(BtsUserGroup)] AS [dbo];
GO

--Add Default UDDI Record
INSERT INTO [UDDI_Admin]
(
	[Anonymous]
	, [AutoPublish]
	, [CategorySchema]
	, [ContactEmail]
	, [ContactName]
	, [ID]
	, [LdapRoot]
	, [NotificationEmail]
	, [NotificationEnabled]
	, [ServiceInterval]
	, [SMTPBody]
	, [SMTPFromEmail]
	, [SMTPServer]
	, [SMTPSubject]
	, [UDDIServer]
)
VALUES
(
	1
	, 1
	, 'microsoft-com:esb:runtimeresolution'
	, 'administrator@domain.com'
	, 'Administrator'
	, 1
	, 'LDAP://domaincontroller.domain.com/DC=domain, DC=com'
	, NULL
	, 0
	, 60000
	, 'C:\Projects\Microsoft.Practices.ESB\Source\Samples\Management Portal\ESB.AlertService\Templates\EmailTransformation.xslt'
	, 'esb@domain.com'
	, 'YouSmtpServer'
	, 'ESB UDDI Publishing Request'
	, 'http://localhost/uddi'
)
GO

-- Add Current User as Portal Admin
DECLARE @RC int
DECLARE @pUserOrGroupName nvarchar(100)
DECLARE @pAccessPolicy xml
DECLARE @pUser nvarchar(50)

SET @pUserOrGroupName = ORIGINAL_LOGIN()
SET @pUser = ORIGINAL_LOGIN()
SET @pAccessPolicy = N'<PolicyDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <BizTalkApplications>
    <string>BizTalk Application 1</string>
    <string>BizTalk.ESB.Extensions</string>
    <string>BizTalk.System</string>
    <string>Microsoft.Practices.ESB</string>
    <string>Microsoft.Practices.JMS</string>
  </BizTalkApplications>
  <EsbItineraries>
  </EsbItineraries>
  <SsoConfigurationApplications>
  </SsoConfigurationApplications>
  <ViewAllErrors>false</ViewAllErrors>
  <ResubmitAllMessages>false</ResubmitAllMessages>
  <ViewDelegatedErrors>true</ViewDelegatedErrors>
  <ResubmitDelegatedMessages>true</ResubmitDelegatedMessages>
  <ViewAllMonitoring>false</ViewAllMonitoring>
  <ViewDeletedMonitoring>true</ViewDeletedMonitoring>
  <ManageAllSso>false</ManageAllSso>
  <ManageDelegatedSso>true</ManageDelegatedSso>
  <ManageAllAlerts>false</ManageAllAlerts>
  <ManageDelegatedAlerts>true</ManageDelegatedAlerts>
  <ManageAllAccessPolicies>false</ManageAllAccessPolicies>
  <ManageDelegatedAccessPolicies>false</ManageDelegatedAccessPolicies>
  <ManageAllItineraries>false</ManageAllItineraries>
  <ManageDelegatedItineraries>true</ManageDelegatedItineraries>
  <PortalAdministrator>true</PortalAdministrator>
</PolicyDetails>'

EXECUTE @RC = [dbo].[usp_set_accesspolicy] 
   @pUserOrGroupName
  ,@pAccessPolicy
  ,@pUser
GO